-- @owner: opentestcase027
-- @date: 2022/5/13
-- @testpoint: 自定义转换函数，加入第二个非必填参数。自定义转换关系implicit模式下insert into操作，隐含调用插入数据成功

--step1:建立自定义类型转换函数;expect:创建函数成功
create or replace function timezone_to_secondsinteger
(
    in time with time zone,
    in integer
)
returns integer
as
$$
declare
seconds integer;
begin
select (
extract(hour from $1)::integer * 3600
+ extract(minute from $1)::integer * 60
+ extract(seconds from $1)::integer* 1)*$2 into seconds;
return seconds;
end;
$$ language plpgsql;
/

--step2:创建赋值模式下隐式转换关系;expect:创建转换关系成功
drop cast if exists (time with time zone as integer) cascade;
create cast(time with time zone as integer) with function timezone_to_secondsinteger(time with time zone,integer) as implicit;


--step3:建行存表;expect:建表成功
drop table if exists t_create_cast_0025 cascade;
create table t_create_cast_0025(i int);

--step4:隐含调用插入数据;expect:插入数据成功
insert into t_create_cast_0025 values(time with time zone '02:02:02 pst');
select * from t_create_cast_0025;

--step5:清理环境;expect:清理成功
drop cast if exists (time with time zone as integer) cascade;
drop function if exists timezone_to_secondsinteger(time with time zone,integer) cascade;
drop table if exists t_create_cast_0025;
